﻿Imports TNCDAL
Imports Excel = Microsoft.Office.Interop.Excel
Imports DevComponents.DotNetBar.Controls

Partial Public Class CViewer

#Region "Bind Combobox"
    Public Shared Sub BindGoodToComboBox(ByVal cbo As ComboBox)
        'lấy danh sách các loại hàng hoá
        Dim ctrl As New MGoods
        Dim list As IQueryable = ctrl.getGoodsList2()
        cbo.DisplayMember = "GoodName"
        cbo.ValueMember = "ID_Good"
        cbo.DataSource = list
        cbo.AutoCompleteMode = AutoCompleteMode.SuggestAppend
        cbo.AutoCompleteSource = AutoCompleteSource.ListItems
    End Sub
    Public Shared Sub BindUnitToComboBox(ByVal cbo As ComboBox)
        'lấy danh sách các đơn vị tính
        Dim ctrl As New CUnit
        Dim list As IQueryable = ctrl.SelectAllRecord()
        cbo.DisplayMember = "UnitName"
        cbo.ValueMember = "ID_Unit"
        cbo.DataSource = list
        cbo.AutoCompleteMode = AutoCompleteMode.SuggestAppend
        cbo.AutoCompleteSource = AutoCompleteSource.ListItems
    End Sub
    Public Shared Sub BindRoleToComboBox(ByVal cbo As ComboBox)
        'lấy danh sách các đơn role
        Dim ctrl As New CRoleController
        Dim list As IQueryable = ctrl.SelectAllRecord()
        'đặt thuộc tính DisplayMember và ValueMember trước thì hàm SelectIndexChange sẽ đúng
        'bởi vì hàm SelectIndexChange sẽ được gọi ngay sau khi set thuộc tính DataSource, nên nếu chưa thiết lập thuộc tính DisplayMember và ValueMember thì việc lấy id=cbo.SelectValue sẽ bị sai
        cbo.DisplayMember = "RoleName"
        cbo.ValueMember = "ID_Role"
        cbo.DataSource = list
        cbo.AutoCompleteMode = AutoCompleteMode.SuggestAppend
        cbo.AutoCompleteSource = AutoCompleteSource.ListItems
    End Sub
    Public Shared Sub BindDemDetToComboBox(ByVal cbo As ComboBox)
        Dim ctrlDemDet As New DemDetRepository
        Dim listDemDet As IQueryable(Of tblDemDet) = ctrlDemDet.SelectAllRecord()
        cbo.DisplayMember = "DemDetName"
        cbo.ValueMember = "ID_DemDet"
        cbo.DataSource = listDemDet
    End Sub
    Public Shared Sub BindDemDetToGridComboBox(ByVal cbo As DataGridViewComboBoxColumn)
        Dim ctrlDemDet As New DemDetRepository
        Dim listDemDet As IQueryable(Of tblDemDet) = ctrlDemDet.SelectAllRecord()
        cbo.DisplayMember = "DemDetName"
        cbo.ValueMember = "ID_DemDet"
        cbo.DataSource = listDemDet
    End Sub
    Public Shared Sub BindContTypeToComboBox(ByVal cbo As ComboBox)
        Dim ctrlContType As New ContTypeRepository
        Dim listType As IQueryable(Of tblContType) = ctrlContType.SelectAllRecord()
        'hiển thị tất cả các container có liên quan đến lô hàng hoặc booking đó
        cbo.DisplayMember = "ContTypeName"
        cbo.ValueMember = "ID_ContType"
        cbo.DataSource = listType
        cbo.AutoCompleteMode = AutoCompleteMode.SuggestAppend
        cbo.AutoCompleteSource = AutoCompleteSource.ListItems
    End Sub
    Public Shared Sub BindContTypeToGridComboBox(ByVal cbo As DataGridViewComboBoxColumn)
        Dim ctrlContType As New ContTypeRepository
        Dim listType As IQueryable(Of tblContType) = ctrlContType.SelectAllRecord()
        'hiển thị tất cả các container có liên quan đến lô hàng hoặc booking đó
        cbo.DisplayMember = "ContTypeName"
        cbo.ValueMember = "ID_ContType"
        cbo.DataSource = listType
    End Sub
    Public Shared Sub BindDriversToComboBox(ByVal cbo As ComboBox)
        Dim ctrlDriver As New MDrivers(GeneralClassDAL.dc)
        Dim listDrivers As IQueryable(Of tblDriver) = ctrlDriver.getDriversList1
        cbo.DisplayMember = "DriverName"
        cbo.ValueMember = "ID_Driver"
        cbo.DataSource = listDrivers
        cbo.AutoCompleteSource = AutoCompleteSource.ListItems
        cbo.AutoCompleteMode = AutoCompleteMode.SuggestAppend
    End Sub
    Public Shared Sub BindHBLsToComboBox(ByVal cbo As ComboBox, ByVal idFile As Integer)
        Dim ctrlHBL As New CHBLController
        Dim listHBL As IQueryable = ctrlHBL.SelectAllRecordByFile(idFile)
        cbo.DisplayMember = "HBLNo"
        cbo.ValueMember = "ID_HBL"
        cbo.DataSource = listHBL
        cbo.AutoCompleteMode = AutoCompleteMode.SuggestAppend
        cbo.AutoCompleteSource = AutoCompleteSource.ListItems
    End Sub
    Public Shared Sub BindCurrencyToComboBox(ByVal cbo As ComboBox)
        'lấy danh sách các ngoại tệ
        Dim ctrl As New MCurrencies
        Dim list As IQueryable = ctrl.getCurrenciesList2
        cbo.DisplayMember = "Code"
        cbo.ValueMember = "ID_Currency"
        cbo.DataSource = list
        cbo.AutoCompleteMode = AutoCompleteMode.SuggestAppend
        cbo.AutoCompleteSource = AutoCompleteSource.ListItems
    End Sub
   Public Shared Sub BindCurrencyToComboBoxSelling(ByVal cbo As ComboBox, ByVal idHBL As Integer)
        Dim context As New TanNamChinhDataContext
        'lấy danh sách các ngoại tệ
        Dim hbl As tblHBL = (From h In context.tblHBLs Where h.ID_HBL = idHBL).First
        Dim list As IQueryable = (From cur In context.tblCurrencies
                                  Where cur.Code = "VND" Or cur.ID_Currency = hbl.tblFileMBL.tblFile.ID_Currency
                                  Select cur)
        cbo.DisplayMember = "Code"
        cbo.ValueMember = "ID_Currency"
        cbo.DataSource = list
        cbo.AutoCompleteMode = AutoCompleteMode.SuggestAppend
        cbo.AutoCompleteSource = AutoCompleteSource.ListItems
    End Sub
    Public Shared Sub BindLinesToComboBox(ByVal cbo As ComboBox)
        Dim ctrlLine As New CLine
        Dim listType As IQueryable(Of tblLine) = ctrlLine.SelectAllRecord()
        'hiển thị tất cả các container có liên quan đến lô hàng hoặc booking đó
        cbo.DisplayMember = "LineName"
        cbo.ValueMember = "ID_Line"
        cbo.DataSource = listType
        cbo.AutoCompleteMode = AutoCompleteMode.SuggestAppend
        cbo.AutoCompleteSource = AutoCompleteSource.ListItems
    End Sub
    Public Shared Sub BindCountriesToComboBox(ByVal cbo As ComboBox)
        Dim ctrlCountries As New CCountryController
        Dim listType As IQueryable(Of tblCountry) = ctrlCountries.SelectAllRecord()
        'hiển thị tất cả các container có liên quan đến lô hàng hoặc booking đó
        cbo.DisplayMember = "CountryName"
        cbo.ValueMember = "ID_Country"
        cbo.DataSource = listType
        cbo.AutoCompleteMode = AutoCompleteMode.SuggestAppend
        cbo.AutoCompleteSource = AutoCompleteSource.ListItems
    End Sub
    Public Shared Sub BindCountriesToCombobox_2(ByVal cbo As ComboBox)
        Dim ctrlCountries As New CCountryController
        Dim countries = ctrlCountries.SelectAllRecord
        Dim emptyTable As tblCountry() = {New tblCountry With {.ID_Country = 0, .CountryName = "-------"}}
        Dim countries2 = (From e In emptyTable Select e).Union(countries).ToList
        With cbo
            .DisplayMember = "CountryName"
            .ValueMember = "ID_Country"
            .DataSource = countries2
            .AutoCompleteMode = AutoCompleteMode.SuggestAppend
            .AutoCompleteSource = AutoCompleteSource.ListItems
        End With
    End Sub
#End Region

#Region "Bind grid"
    Public Shared Sub BindGridSelling(ByVal gridSelling As DataGridViewX, ByVal idHBL As Integer, ByVal cboCurr As DataGridViewComboBoxColumn)
        Dim uti As New Utilities
        uti.FillCurrencyCombo(cboCurr)
        Dim ctrlSelling As New CSellingRateController
        Dim sellings = ctrlSelling.GetSellingRateList2(idHBL)
        gridSelling.DataSource = sellings
        'Format gridview
        gridSelling.AutoSizeColumnsMode = System.Windows.Forms.DataGridViewAutoSizeColumnsMode.DisplayedCells
        gridSelling.AutoSizeRowsMode = System.Windows.Forms.DataGridViewAutoSizeRowsMode.None
        gridSelling.AllowUserToAddRows = False
        gridSelling.AllowUserToDeleteRows = False
        gridSelling.EditMode = DataGridViewEditMode.EditOnEnter
        gridSelling.Columns("ID_SellingFee").Visible = False
        gridSelling.Columns("FeeName").HeaderText = "Fee Name"
        gridSelling.Columns("FeeName").Width = 80
        gridSelling.Columns("UnitPrice").Width = 60
        gridSelling.Columns("UnitPrice").DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
        gridSelling.Columns("UnitPrice").DefaultCellStyle.Format = "#,##0.#####"
        gridSelling.Columns("Quantity").HeaderText = "Qty"
        gridSelling.Columns("Quantity").Width = 30
        gridSelling.Columns("Quantity").DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
        gridSelling.Columns("Quantity").DefaultCellStyle.Format = "#,##0.#####"
        gridSelling.Columns("Price").Width = 80
        gridSelling.Columns("Price").DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
        gridSelling.Columns("Price").DefaultCellStyle.Format = "#,##0.#####"
        gridSelling.Columns("cboCurr").Width = 40
        gridSelling.Columns("cboCurr").HeaderText = "Curr"
        gridSelling.Columns("Description").Width = 200
        gridSelling.ScrollBars = 3
    End Sub
#End Region

#Region "IN HBL"
    Public Shared Sub PrintHBL(ByVal idHBL As Integer)
        If (idHBL > 0) Then
            'Dim xlApp As Excel.Application
            'xlApp = New Excel.Application
            Dim frm As New frmHBLOption(idHBL)
            frm.ShowDialog()
        End If
    End Sub
    Public Shared Sub PrintHBL_2(ByVal idHBL As Integer, ByVal textOption As String)
        'hiển thị tùy chọn khi in
        Dim ctrl As New CHBLController
        Dim obj As tblHBL = ctrl.SelectRecordByID(idHBL)

        Dim xlApp As Excel.Application
        Dim xlWorkBook As Excel.Workbook
        Dim xlWorkSheet As Excel.Worksheet

        xlApp = New Excel.Application
        xlWorkBook = xlApp.Workbooks.Open(Application.StartupPath & "\HBL_template.xltx")
        xlWorkSheet = xlWorkBook.Worksheets(1)


        'FORMAT EXCEL
        'remove all page breaks
        xlWorkSheet.ResetAllPageBreaks()
        'set paper orientation
        xlWorkSheet.PageSetup.Orientation = Excel.XlPageOrientation.xlPortrait
        xlWorkSheet.PageSetup.PaperSize = Excel.XlPaperSize.xlPaperA4

        'mở ra cho người ta xem
        xlApp.Visible = True

        'IN CHỮ THÊM SAU BILL OF LADING
        If (textOption <> "") Then
            xlWorkSheet.Range("F3").Value = textOption
            xlWorkSheet.Range("F3").Font.Bold = False
            xlWorkSheet.Range("F3").Font.Size = 12
            xlWorkSheet.Range("F3").Font.Name = "Arial"
            xlWorkSheet.Range("F3").VerticalAlignment = Excel.XlVAlign.xlVAlignTop
        End If

        'thực hiện hiển thị ra Excel ở đây
        Dim overShipper As Integer = 0
        Dim overConsignee As Integer = 0
        Dim overNotify As Integer = 0

        'ĐÂY LÀ NƠI LƯU THÔNG TIN VỀ SỐ KÝ TỰ TỐI ĐA CỦA TỪNG CỘT EXCEL
        Dim nKyTuCotShipper As Integer = 51 'Số ký tự tối đa của cột shipper, consignee, notify party
        Dim nKyTuCotDomestic As Integer = 44 'Số ký tự tối đa của cột Domestic tức là cột Agent
        Dim nKyTuCont As Integer = 33
        Dim nKyTuDes As Integer = 37


        'HIỂN THỊ SHIPPER
        'tính ra số dòng của Shipper
        Dim arrShipper As New ArrayList
        SplitString2(obj.tblFileMBL.tblFile.tblCompany.Name, nKyTuCotShipper, arrShipper)
        SplitString2(obj.tblFileMBL.tblFile.tblCompany.Address, nKyTuCotShipper, arrShipper)
        overShipper = arrShipper.Count - 3
        If (overShipper < 0) Then
            overShipper = 0
        End If

        For i As Integer = 0 To overShipper - 1
            xlWorkSheet.Rows(8).Insert()
        Next i

        arrShipper.Add("TEL:" & obj.tblFileMBL.tblFile.tblCompany.Tel & " - " & "FAX:" & obj.tblFileMBL.tblFile.tblCompany.Fax)
        'bắt đầu in thông tin shipper
        Dim posShipper As Integer = 5
        For Each s As String In arrShipper
            xlWorkSheet.Range("A" & posShipper.ToString()).Value = s
            xlWorkSheet.Range("A" & posShipper.ToString()).WrapText = False
            posShipper = posShipper + 1
        Next

        'format nó với font: arial, 10px, bold
        xlWorkSheet.Range("A5:A" & (posShipper - 1).ToString()).Font.Bold = True
        xlWorkSheet.Range("A5:A" & (posShipper - 1).ToString()).Font.Size = 10
        xlWorkSheet.Range("A5:A" & (posShipper - 1).ToString()).Font.Name = "Arial"

        'HIỂN THỊ CONSIGNEE
        'tính ra số dòng của Consignee
        Dim arrConsigneer As New ArrayList
        SplitString2(obj.tblFileMBL.tblFile.tblForeignCompany.Name, nKyTuCotShipper, arrConsigneer)
        SplitString2(obj.tblFileMBL.tblFile.tblForeignCompany.Address, nKyTuCotShipper, arrConsigneer)

        overConsignee = arrConsigneer.Count - 4
        If (overConsignee < 0) Then
            overConsignee = 0
        End If
        For i As Integer = 0 To overConsignee - 1
            xlWorkSheet.Rows(14 + overShipper).Insert()
        Next i

        arrConsigneer.Add("TEL:" & obj.tblFileMBL.tblFile.tblForeignCompany.Tel & " - " & "FAX:" & obj.tblFileMBL.tblFile.tblForeignCompany.Fax)
        'bắt đầu in thông tin Consignee
        Dim posConsignee As Integer = 10 + overShipper
        For Each s As String In arrConsigneer
            xlWorkSheet.Range("A" & posConsignee.ToString()).Value = s
            xlWorkSheet.Range("A" & posConsignee.ToString()).WrapText = False
            posConsignee = posConsignee + 1
        Next
        'format nó với font: arial, 10px, bold
        xlWorkSheet.Range("A" & (10 + overShipper).ToString & ":A" & (posConsignee - 1).ToString()).Font.Bold = True
        xlWorkSheet.Range("A" & (10 + overShipper).ToString & ":A" & (posConsignee - 1).ToString()).Font.Size = 10
        xlWorkSheet.Range("A" & (10 + overShipper).ToString & ":A" & (posConsignee - 1).ToString()).Font.Name = "Arial"

        'HIỂN THỊ NOTIFY PARTY
        'đầu tiên là tách nó ra từng dòng
        Dim textNotify() As String = Split(obj.NotifyParty, vbNewLine)
        Dim arrNotify As New ArrayList
        For Each textNote As String In textNotify
            SplitString2(textNote, nKyTuCotShipper, arrNotify)
        Next

        'HIỂN THỊ THÔNG TIN AGENT
        Dim arrAgent As New ArrayList
        If (obj.tblFileMBL.tblFile.tblAgent IsNot Nothing) Then
            SplitString2(obj.tblFileMBL.tblFile.tblAgent.Name, nKyTuCotDomestic, arrAgent)
            SplitString2(obj.tblFileMBL.tblFile.tblAgent.Address, nKyTuCotDomestic, arrAgent)
            arrAgent.Add("TEL:" & obj.tblFileMBL.tblFile.tblAgent.Tel & " - FAX:" & obj.tblFileMBL.tblFile.tblAgent.Fax)
        End If
        If (arrAgent.Count > arrNotify.Count) Then
            overNotify = arrAgent.Count - 4
        Else
            overNotify = arrNotify.Count - 4
        End If
        If (overNotify < 0) Then
            overNotify = 0
        End If
        For i As Integer = 0 To overNotify - 1
            xlWorkSheet.Rows(20 + overShipper + overConsignee).Insert()
        Next i
        'bắt đầu in thông tin notify party
        Dim posNotify As Integer = 16 + overShipper + overConsignee
        For Each s As String In arrNotify
            xlWorkSheet.Range("A" & posNotify.ToString()).Value = s
            xlWorkSheet.Range("A" & posNotify.ToString()).WrapText = False
            posNotify = posNotify + 1
        Next
        If (arrNotify.Count > 0) Then
            'format nó với font: arial, 10px, bold
            xlWorkSheet.Range("A" & (16 + overShipper + overConsignee).ToString & ":A" & (posNotify - 1).ToString()).Font.Bold = True
            xlWorkSheet.Range("A" & (16 + overShipper + overConsignee).ToString & ":A" & (posNotify - 1).ToString()).Font.Size = 10
            xlWorkSheet.Range("A" & (16 + overShipper + overConsignee).ToString & ":A" & (posNotify - 1).ToString()).Font.Name = "Arial"
        End If

        'bắt đầu in thông tin agent
        Dim posAgent As Integer = 16 + overShipper + overConsignee
        For Each s As String In arrAgent
            xlWorkSheet.Range("F" & posAgent.ToString()).Value = s
            xlWorkSheet.Range("F" & posAgent.ToString()).WrapText = False
            posAgent = posAgent + 1
        Next
        If (arrAgent.Count > 0) Then
            'format nó với font: arial, 10px, bold
            xlWorkSheet.Range("F" & (16 + overShipper + overConsignee).ToString & ":F" & (posAgent - 1).ToString()).Font.Bold = True
            xlWorkSheet.Range("F" & (16 + overShipper + overConsignee).ToString & ":F" & (posAgent - 1).ToString()).Font.Size = 10
            xlWorkSheet.Range("F" & (16 + overShipper + overConsignee).ToString & ":F" & (posAgent - 1).ToString()).Font.Name = "Arial"
        End If

        'booking no
        'đây là HBL no
        'nếu HBL là is master thì nó là MBL no
        If (obj.IsMaster) Then
            xlWorkSheet.Range("g5").Value = obj.tblFileMBL.tblMBL.MBLNo
        Else
            xlWorkSheet.Range("g5").Value = obj.HBLNo
        End If

        Try
            'vessel và voyage no
            xlWorkSheet.Range("A" & (22 + overShipper + overConsignee + overNotify).ToString).Value = obj.tblFileMBL.tblMBL.tblVoyage.tblVessel.VesselName & " " & obj.tblFileMBL.tblMBL.tblVoyage.VoyageNo
        Catch ex As Exception
        End Try
        
        Try
            'POL
            xlWorkSheet.Range("D" & (22 + overShipper + overConsignee + overNotify).ToString).Value = obj.tblFileMBL.tblMBL.tblVoyage.tblPort.Port & ", " & obj.tblFileMBL.tblMBL.tblVoyage.tblPort.tblCountry.CountryName
        Catch ex As Exception
        End Try

        Try
            'POD
            xlWorkSheet.Range("A" & (24 + overShipper + overConsignee + overNotify).ToString).Value = obj.tblFileMBL.tblMBL.tblVoyage.tblPort1.Port & ", " & obj.tblFileMBL.tblMBL.tblVoyage.tblPort1.tblCountry.CountryName
        Catch ex As Exception
        End Try

        Dim sNgayTau As String = ""
        Try
            If (obj.tblFileMBL.tblMBL.tblVoyage.ETD.HasValue) Then
                sNgayTau = obj.tblFileMBL.tblMBL.tblVoyage.ETD.Value.ToString("MMM dd,yyyy")
                xlWorkSheet.Range("G53").Value = sNgayTau
                xlWorkSheet.Range("G53").Font.Bold = True
            End If
        Catch ex As Exception
        End Try


        'hiển thị chữ freight prepaid hoặc freight collect
        Dim term As String = obj.tblFileMBL.tblFile.Term.Trim
        Try
            If (term.Substring(2, 2) = "CC") Then
                xlWorkSheet.Range("A46").Value = """FREIGHT COLLECT"""
            Else
                xlWorkSheet.Range("A46").Value = """FREIGHT PREPAID"""
            End If
            xlWorkSheet.Range("A46").Font.Bold = True
        Catch ex As Exception
        End Try


        'PHẦN THÔNG TIN CHI TIẾT HBL CHUẨN CHIẾM 15 DÒNG
        'Tuy nhiên, khi phần thông tin Header tăng thì phần thông tin chi tiết bị giảm đi tương ứng
        Dim nRowsChiTiet As Integer = 15 - overShipper - overConsignee - overNotify
        'TIẾN HÀNH XOÁ DÒNG Ở PHẦN CHI TIẾT TƯƠNG ỨNG VỚI PHẦN TĂNG THÊM CỦA HEADER
        For i As Integer = 1 To (overShipper + overConsignee + overNotify)
            xlWorkSheet.Rows(27 + overShipper + overConsignee + overNotify).Delete()
        Next i
        'PHẦN THÔNG TIN MARKS VÀ CONTAINERS
        Dim arrMarksAndConts As New ArrayList
        arrMarksAndConts.Add("CONT/SEAL NO:")
        'thông tin containers
        'hiển thị thông tin cont ở đây
        Dim sumCBM As Double = 0
        For Each tblCont In obj.tblConts
            sumCBM = sumCBM + tblCont.CBM.GetValueOrDefault
            Dim s As String = tblCont.ContNo & " / " & tblCont.Seal & " / " & tblCont.tblContType.ContTypeName
            arrMarksAndConts.Add(s)
        Next
        'thông tin mark
        arrMarksAndConts.Add("MARKS:")
        Dim textMarks = obj.MarkNumber.Split(New String() {vbCr, vbLf}, StringSplitOptions.RemoveEmptyEntries)
        For Each textMark As String In textMarks
            SplitString2(textMark, nKyTuCont, arrMarksAndConts)
        Next

        'PHẦN THÔNG TIN DESCRIPTION
        Dim arrDes As New ArrayList
        'đầu tiên là thông tin về loại cont 
        'lấy thông tin tổng số lượng từng loại cont
        Dim db As New TanNamChinhDataContext
        Dim Cont_ContType As String = ""
        Dim Say As String = ""
        Dim ListCont = From p In db.tblConts
                       Join q In db.tblContTypes On p.ID_ContType Equals q.ID_ContType
                       Where p.ID_HBL = idHBL
                       Group p By p.ID_ContType, q.ContTypeName Into Group
                       Select New With
                              {
                                  .ID_ContType = ID_ContType,
                                  .ContTypeName = ContTypeName,
                                  .Count = Group.Count
                              }

        For Each cont In ListCont
            If cont.Count < 10 Then
                If cont.ContTypeName.Substring(1, 2) = "20" Then
                    Say += " &" + CModule_Sy.NumToStringEnglish(cont.Count).ToUpper + "(0" + cont.Count.ToString + ")" + "TWENTY-FEET CONTAINER(S)"
                ElseIf cont.ContTypeName.Substring(1, 2) = "40" Then
                    Say += " &" + CModule_Sy.NumToStringEnglish(cont.Count).ToUpper + "(0" + cont.Count.ToString + ")" + "FORTY-FEET CONTAINER(S)"
                Else
                    Say += " &" + CModule_Sy.NumToStringEnglish(cont.Count).ToUpper + "(0" + cont.Count.ToString + ")" + "?-FEET CONTAINER(S)"
                End If
                Cont_ContType += " & " + "(0" + cont.Count.ToString + ")" + cont.ContTypeName
            Else
                If cont.ContTypeName.Substring(1, 2) = "20" Then
                    Say += " &" + CModule_Sy.NumToStringEnglish(cont.Count).ToUpper + "(0" + cont.Count.ToString + ")" + "TWENTY-FEET CONTAINER(S)"
                ElseIf cont.ContTypeName.Substring(1, 2) = "40" Then
                    Say += " &" + CModule_Sy.NumToStringEnglish(cont.Count).ToUpper + "(0" + cont.Count.ToString + ")" + "FORTY-FEET CONTAINER(S)"
                Else
                    Say += " &" + CModule_Sy.NumToStringEnglish(cont.Count).ToUpper + "(0" + cont.Count.ToString + ")" + "?-FEET CONTAINER(S)"
                End If
                Cont_ContType += " & " + "(" + cont.Count.ToString + ")" + cont.ContTypeName
            End If
        Next

        'hiển thị thông tin loại cont
        If (Cont_ContType.Length > 3) Then
            Cont_ContType = Cont_ContType.Substring(3, Cont_ContType.Length - 3)
        End If

        If obj.tblFileMBL.tblFile.IsFCL Then
            Cont_ContType = Cont_ContType + " (FCL) S.T.C"
        Else
            Cont_ContType = Cont_ContType + " (LCL) S.T.C"
        End If

        If obj.tblFileMBL.tblFile.IsD2D Then
            Cont_ContType = Cont_ContType + "  D/D"
        Else ' CY/CY
            Cont_ContType = Cont_ContType + "  CY/CY"
        End If
        'thêm vào arr description
        SplitString2(Cont_ContType, nKyTuDes, arrDes)
        'thông tin description
        Dim textDesription = obj.GoodDescription.Split(New String() {vbCr, vbLf}, StringSplitOptions.RemoveEmptyEntries)
        For Each textDes As String In textDesription
            SplitString2(textDes, nKyTuDes, arrDes)
        Next

        'hiển thị dòng thông tin
        arrDes.Add("SHIPPER’S PACK, LOAD, COUNT AND SEAL,")
        arrDes.Add("SAID TO CONTAINER")

        'hiển thị dòng SAY
        If (Say.Length > 3) Then
            Say = Say.Substring(3, Say.Length - 3)
            Say = "SAY: " + Say
            SplitString2(Say, nKyTuDes, arrDes)
        End If

        'TÍNH RA SỐ DÒNG ĐỂ HIỂN THỊ PHẦN THÔNG TIN CHI TIẾT HBL
        Dim nCountChiTiet As Integer = arrMarksAndConts.Count
        If (arrDes.Count > nCountChiTiet) Then
            nCountChiTiet = arrDes.Count
        End If

        'TÍNH SỐ TRANG CẦN PHẢI CÓ ĐỂ LƯU PHẦN CHI TIẾT HBL NÀY
        Dim nPages As Integer = nCountChiTiet \ nRowsChiTiet
        If (nCountChiTiet Mod nRowsChiTiet <> 0) Then
            nPages = nPages + 1
        End If
        'THỰC HIỆN COPY SANG TRANG KHÁC
        For i As Integer = 1 To nPages - 1
            'THỰC HIỆN BREAK PAGE Ở ĐÂY
            xlWorkSheet.HPageBreaks.Add(xlWorkSheet.Range("A" & i * 53 + 1))
            CopyMau(i, xlWorkSheet, overShipper, overConsignee, overNotify)
        Next

        'IN PHẦN CONTAINERS VÀ MARKS
        For i As Integer = 0 To arrMarksAndConts.Count - 1
            Dim ipage As Integer = i \ nRowsChiTiet
            Dim pos As Integer = (53 * ipage + 27 + (i - ipage * nRowsChiTiet) + overShipper + overConsignee + overNotify)
            xlWorkSheet.Range("A" & pos.ToString).Value = arrMarksAndConts(i)
            'format
            xlWorkSheet.Range("A" & pos.ToString).WrapText = False
            'kiểm tra nếu đó là dòng MARK thì underline
            If arrMarksAndConts(i) = "MARKS:" Or arrMarksAndConts(i) = "CONT/SEAL NO:" Then
                xlWorkSheet.Range("A" & pos.ToString).Font.Underline = True
            Else
                xlWorkSheet.Range("A" & pos.ToString).Font.Underline = False
            End If

            xlWorkSheet.Range("A" & pos.ToString).Font.Italic = False
            xlWorkSheet.Range("A" & pos.ToString).Font.Bold = True
            xlWorkSheet.Range("A" & pos.ToString).Font.Name = "Arial"
            xlWorkSheet.Range("A" & pos.ToString).Font.Size = 10
            xlWorkSheet.Range("A" & pos.ToString).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft
        Next
        'IN PHẦN DES
        For i As Integer = 0 To arrDes.Count - 1
            Dim ipage As Integer = i \ nRowsChiTiet
            Dim pos As Integer = (53 * ipage + 27 + (i - ipage * nRowsChiTiet) + overShipper + overConsignee + overNotify)
            xlWorkSheet.Range("D" & pos.ToString).Value = arrDes(i)
            xlWorkSheet.Range("D" & pos.ToString).WrapText = False
            xlWorkSheet.Range("D" & pos.ToString).Font.Underline = False
            xlWorkSheet.Range("D" & pos.ToString).Font.Italic = False
            xlWorkSheet.Range("D" & pos.ToString).Font.Bold = True
            xlWorkSheet.Range("D" & pos.ToString).Font.Name = "Arial"
            xlWorkSheet.Range("D" & pos.ToString).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft
        Next

        'tổng khối lượng: GW
        xlWorkSheet.Range("G" & (28 + overShipper + overConsignee + overNotify).ToString).Value = "GW: " & obj.GW.Value.ToString("#,##0.##") & " KGS"
        xlWorkSheet.Range("G" & (28 + overShipper + overConsignee + overNotify).ToString).Font.Bold = True

        'NW:
        xlWorkSheet.Range("G" & (29 + overShipper + overConsignee + overNotify).ToString).Value = "NW: " & obj.NW.Value.ToString("#,##0.##") & " KGS"
        xlWorkSheet.Range("G" & (29 + overShipper + overConsignee + overNotify).ToString).Font.Bold = True

        'tổng CBM
        Dim posCBM As Integer = 28 + overShipper + overConsignee + overNotify
        xlWorkSheet.Range("H" & posCBM.ToString).Value = sumCBM.ToString("#,##0.##") & " CBM"
        xlWorkSheet.Range("H" & posCBM.ToString).HorizontalAlignment = Excel.XlHAlign.xlHAlignRight
        xlWorkSheet.Range("H" & posCBM.ToString).Font.Bold = True

        'một số phần thông tin thêm
        'chữ CLEAN ON BOARD
        xlWorkSheet.Range("H" & (posCBM + 2).ToString).Value = "CLEAN ON BOARD"

        'tên tàu
        xlWorkSheet.Range("H" & (posCBM + 3).ToString).Value = obj.tblFileMBL.tblMBL.tblVoyage.tblLine.LineName & " " & obj.tblFileMBL.tblMBL.tblVoyage.VoyageNo

        'ngày tàu đến
        xlWorkSheet.Range("H" & (posCBM + 4).ToString).Value = sNgayTau

        xlWorkSheet.Range("H" & (posCBM + 6).ToString).Value = "AS AGENT FOR CARRIER"

        xlWorkSheet.Range("H" & (posCBM + 7).ToString).Value = "TANACHI CO.,LTD"

        xlWorkSheet.Range("H" & (posCBM + 2).ToString & ":H" & (posCBM + 7).ToString).Font.Bold = True
        xlWorkSheet.Range("H" & (posCBM + 2).ToString & ":H" & (posCBM + 7).ToString).Font.Size = 10
        xlWorkSheet.Range("H" & (posCBM + 2).ToString & ":H" & (posCBM + 7).ToString).HorizontalAlignment = Excel.XlHAlign.xlHAlignRight

        'danh so trang
        xlWorkSheet.PageSetup.CenterFooter = "&P/&N"
    End Sub

    Public Shared Sub CopyMau(ByVal nPage As Integer, ByRef xlSheet As Excel.Worksheet, ByVal overShipper As Integer, ByVal overConsignee As Integer, ByVal overNotify As Integer)
        'thực hiện copy phần đầu của HBL sang trang mới
        Dim sourceRange As Excel.Range = xlSheet.Range("A1:H53")
        Dim desRange As Excel.Range = xlSheet.Range("A" & (1 + nPage * 53))
        sourceRange.Copy(desRange)
        'CHỈNH LẠI KÍCH THƯỚC 1 SỐ DÒNG
        'dòng tương ứng với dòng 1
        xlSheet.Rows(1 + (nPage * 53)).RowHeight = xlSheet.Rows(1).RowHeight
        'dòng tương ứng với dòng 2
        xlSheet.Rows(2 + (nPage * 53)).RowHeight = xlSheet.Rows(2).RowHeight
        'dòng tương ứng với dòng 3
        xlSheet.Rows(3 + (nPage * 53)).RowHeight = xlSheet.Rows(3).RowHeight
        'dòng tương ứng với dòng 26
        xlSheet.Rows(26 + (nPage * 53) + overShipper + overConsignee + overNotify).RowHeight = xlSheet.Rows(26 + overShipper + overConsignee + overNotify).RowHeight
        'dòng tương ứng với dòng 42
        xlSheet.Rows(42 + (nPage * 53)).RowHeight = xlSheet.Rows(42).RowHeight
        'dòng tương ứng với dòng 43
        xlSheet.Rows(43 + (nPage * 53)).RowHeight = xlSheet.Rows(43).RowHeight
        'dòng tương ứng với dòng 48
        xlSheet.Rows(48 + (nPage * 53)).RowHeight = xlSheet.Rows(48).RowHeight
        'dòng tương ứng với dòng 49
        xlSheet.Rows(49 + (nPage * 53)).RowHeight = xlSheet.Rows(49).RowHeight
        'dòng tương ứng với dòng 50
        xlSheet.Rows(50 + (nPage * 53)).RowHeight = xlSheet.Rows(50).RowHeight
        'dòng tương ứng với dòng 51
        xlSheet.Rows(51 + (nPage * 53)).RowHeight = xlSheet.Rows(51).RowHeight
    End Sub

#End Region

#Region "In AN"
    Public Shared Sub PrintAN(ByVal objHBL As tblHBL)
        Dim xlApp As Excel.Application
        Dim xlWorkBook As Excel.Workbook
        Dim xlWorkSheet As Excel.Worksheet

        xlApp = New Excel.Application
        xlWorkBook = xlApp.Workbooks.Open(Application.StartupPath & "\AN_template.xltx")
        xlWorkSheet = xlWorkBook.Worksheets("AN")

        'thực hiện lưu ở đây
        'mở ra cho người ta xem
        xlApp.Visible = True

        'ngày báo
        xlWorkSheet.Range("D10").Value = Date.Now.ToString("dd/MM/yyyy")

        Dim sLoiThongTin As String = "Lỗi in Arrival Note:"
        'thông tin số file
        xlWorkSheet.Range("b11").Value = "F " & objHBL.tblFileMBL.tblFile.FileNo
        Dim thongTinKhachHang = ""
        Try
            'tên khách hàng
            thongTinKhachHang = objHBL.tblFileMBL.tblFile.tblCompany.Name
            'xlWorkSheet.Range("b12").Value = objHBL.tblFileMBL.tblFile.tblCompany.Name
        Catch ex As Exception
            sLoiThongTin &= Environment.NewLine & "Chưa có tên khách hàng"
        End Try
        
        Try
            'địa chỉ
            thongTinKhachHang &= vbNewLine & objHBL.tblFileMBL.tblFile.tblCompany.Address
            'xlWorkSheet.Range("b13").Value = objHBL.tblFileMBL.tblFile.tblCompany.Address
        Catch ex As Exception
            sLoiThongTin &= Environment.NewLine & "Chưa có địa chỉ khách hàng"
        End Try
        xlWorkSheet.Range("b12").Value = thongTinKhachHang
        Try
            'tên tàu
            xlWorkSheet.Range("d16").Value = objHBL.tblFileMBL.tblMBL.tblVoyage.tblVessel.VesselName & " " & objHBL.tblFileMBL.tblMBL.tblVoyage.VoyageNo
        Catch ex As Exception
            sLoiThongTin &= Environment.NewLine & "Chưa có tên tàu"
        End Try
        
        Try
            'cảng nhận hàng
            xlWorkSheet.Range("b18").Value = objHBL.tblFileMBL.tblMBL.tblVoyage.tblPort1.Port
        Catch ex As Exception
            sLoiThongTin &= Environment.NewLine & "Chưa có cảng nhận hàng"
        End Try
        
        Try
            'ngày nhận hàng
            xlWorkSheet.Range("f18").Value = objHBL.tblFileMBL.tblMBL.tblVoyage.ETD.Value.ToString("MMM dd,yyyy")
        Catch ex As Exception
            sLoiThongTin &= Environment.NewLine & "Chưa có ngày nhận hàng"
        End Try
        
        Try
            If (objHBL.IsMaster = True) Then
                'B/L no
                xlWorkSheet.Range("A23").Value = objHBL.tblFileMBL.tblMBL.MBLNo
            Else
                xlWorkSheet.Range("A23").Value = objHBL.HBLNo
            End If
        Catch ex As Exception
            sLoiThongTin &= Environment.NewLine & "Chưa có MBL No"
        End Try
        
        'thông tin container
        Dim rStart As Integer = 23
        Dim ctrlCont As New CContainerController
        For Each cont As tblCont In ctrlCont.SelectContsByHBL(objHBL.ID_HBL)
            If (rStart > 27) Then
                'xlWorkSheet.Range((rStart - 2).ToString & ":" & (rStart - 1).ToString).Insert(Shift:=Excel.XlDirection.xlUp)
                'thêm 2 dòng mới vào
                xlWorkSheet.Rows(rStart - 1).Insert()
                xlWorkSheet.Rows(rStart - 1).Insert()
            End If
            xlWorkSheet.Range("B" & rStart.ToString).Value = cont.ContNo & "/" & cont.Seal
            'số kiện
            If (cont.Package.HasValue And cont.tblUnit IsNot Nothing) Then
                xlWorkSheet.Range("C" & rStart.ToString).Value = cont.Package.Value.ToString() & " " & cont.tblUnit.UnitName
            End If

            'mô tả hàng hóa
            If (cont.tblGood IsNot Nothing) Then
                xlWorkSheet.Range("D" & rStart.ToString).Value = cont.tblGood.GoodName
                xlWorkSheet.Range("D" & rStart.ToString, "E" & rStart.ToString).Merge()
            End If
            If (cont.GW.HasValue) Then
                xlWorkSheet.Range("F" & rStart.ToString).Value = cont.GW.Value.ToString("#,##0.##")
                xlWorkSheet.Range("G" & rStart.ToString).Value = "KGS"
            End If
            If (cont.CBM.HasValue) Then
                xlWorkSheet.Range("F" & (rStart + 1).ToString).Value = cont.CBM.Value.ToString("#,##0.##")
                xlWorkSheet.Range("G" & (rStart + 1).ToString).Value = "CBM"
            End If
            rStart = rStart + 2
        Next
        'format
        xlWorkSheet.Range("A23:" & "G" & (rStart - 1).ToString).Font.Bold = True
        xlWorkSheet.Range("A23:" & "G" & (rStart - 1).ToString).Font.Color = Color.Blue
        xlWorkSheet.Range("A23:" & "G" & (rStart - 1).ToString).Font.Size = 11
        With xlWorkSheet.Range("A23:" & "G" & (rStart - 1).ToString)
            .Font.Bold = True
            .Font.Color = Color.Blue
            .Font.Size = 11
            .VerticalAlignment = Excel.XlVAlign.xlVAlignTop
            .WrapText = True
        End With

        'chi phí
        If (rStart > 29) Then
            rStart = rStart + 10
        Else
            rStart = 39
        End If
        'lấy danh sách phí selling
        Dim sellingCtrl As New CSellingRateController
        'For Each fee As sp_Thanh_GetSellingRateList_2Result In sellingCtrl.GetSellingRateList2(objHBL.ID_HBL)
        '    If (fee.Price.HasValue And fee.Price > 0) Then
        '        xlWorkSheet.Range("A" & rStart.ToString).Value = fee.FeeName
        '        xlWorkSheet.Range("B" & rStart.ToString).Value = fee.Price.Value.ToString("#,##0.##")
        '        Dim ctrlCur As New MCurrencies
        '        If (ctrlCur.GetCurrencyByID(fee.ID_Currency) IsNot Nothing) Then
        '            xlWorkSheet.Range("C" & rStart.ToString).Value = ctrlCur.GetCurrencyByID(fee.ID_Currency).Code
        '        End If
        '        xlWorkSheet.Range("A" & rStart.ToString & ":" & "C" & rStart.ToString).Font.Bold = True
        '        xlWorkSheet.Range("A" & rStart.ToString & ":" & "C" & rStart.ToString).Font.Size = 11
        '        rStart = rStart + 1
        '    End If
        'Next
        'lấy chi tiết giá từng loại cont và in ra theo định dạng: <TÊN TIỀN TỆ> <GIÁ(1 CONT LOẠI 1)>/LOẠI CONT 1 - <GIÁ(1 CONT LOẠI 2)>/LOẠI CONT 2 
        Dim sellFee = sellingCtrl.GetSellingFeeList()
        Dim ctrlCur As New MCurrencies
        'lay thong tin container theo hbl
        Dim contTypeHBL = ctrlCont.SelectContTypeInHBL(objHBL.ID_HBL)
        For Each fee As tblFee In sellFee
            Dim sDes As String = ""
            If fee.IsBill Then
                Dim billFee = sellingCtrl.GetSellingBillFee(objHBL.ID_HBL, fee.ID_Fee)
                If (billFee IsNot Nothing) Then
                    If (billFee.Price.HasValue And billFee.Price > 0) Then
                        sDes = billFee.Price.Value.ToString("#,##0.##")
                        Dim curr As tblCurrency = ctrlCur.GetCurrencyByID(billFee.ID_Currency)
                        If (curr IsNot Nothing) Then
                            sDes = curr.Code.Trim() & " " & sDes
                        End If
                        xlWorkSheet.Range("A" & rStart.ToString).Value = fee.FeeName
                        xlWorkSheet.Range("B" & rStart.ToString).Value = sDes
                        xlWorkSheet.Range("A" & rStart.ToString & ":" & "C" & rStart.ToString).Font.Bold = True
                        xlWorkSheet.Range("A" & rStart.ToString & ":" & "C" & rStart.ToString).Font.Size = 11
                        rStart = rStart + 1
                    End If
                End If
            Else
                For Each contType In contTypeHBL
                    Dim sContFee = ""
                    Dim contFee = sellingCtrl.GetSellingContFee(objHBL.ID_HBL, fee.ID_Fee, contType.ID_ContType)
                    If (contFee IsNot Nothing) Then
                        If (contFee.Price.HasValue And contFee.Price > 0) Then
                            sContFee = contFee.Price.Value.ToString("#,##0.##") & " / " & contType.ContTypeName
                            Dim curr As tblCurrency = ctrlCur.GetCurrencyByID(contFee.ID_Currency)
                            If (curr IsNot Nothing) Then
                                sContFee = curr.Code.Trim() & " " & sContFee
                            End If
                            sDes &= " - " & sContFee
                        End If
                    End If
                Next
                If (sDes.Contains(" - ")) Then
                    sDes = sDes.Remove(0, 3)
                End If
                If (sDes <> "") Then
                    xlWorkSheet.Range("A" & rStart.ToString).Value = fee.FeeName
                    xlWorkSheet.Range("B" & rStart.ToString).Value = sDes
                    xlWorkSheet.Range("A" & rStart.ToString & ":" & "C" & rStart.ToString).Font.Bold = True
                    xlWorkSheet.Range("A" & rStart.ToString & ":" & "C" & rStart.ToString).Font.Size = 11
                    rStart = rStart + 1
                End If
            End If
        Next
        If (sLoiThongTin <> "Lỗi in Arrival Note:") Then
            MessageBox.Show(sLoiThongTin, "Thông báo")
        End If
    End Sub
#End Region

#Region "In DO"
    Public Shared Sub PrintDO(ByVal objHBL As tblHBL)
        Dim xlApp As Excel.Application
        Dim xlWorkBook As Excel.Workbook
        Dim xlWorkSheet As Excel.Worksheet

        xlApp = New Excel.Application
        xlWorkBook = xlApp.Workbooks.Open(Application.StartupPath & "\DO_template.xltx")
        xlWorkSheet = xlWorkBook.Worksheets("DO")

        'thực hiện lưu ở đây
        'mở ra cho người ta xem
        xlApp.Visible = True

        Dim sLoiThongTin As String = "Lỗi in Delivery Order:"
        Dim sthongtinkhachhang = ""
        Try
            'tên khách hàng
            sthongtinkhachhang = objHBL.tblFileMBL.tblFile.tblCompany.Name
            'xlWorkSheet.Range("b13").Value = objHBL.tblFileMBL.tblFile.tblCompany.Name
        Catch ex As Exception
            sLoiThongTin &= Environment.NewLine & "Chưa có tên khách hàng"
        End Try

        Try
            sthongtinkhachhang &= vbNewLine & objHBL.tblFileMBL.tblFile.tblCompany.Address
            'địa chỉ
            'xlWorkSheet.Range("b14").Value = objHBL.tblFileMBL.tblFile.tblCompany.Address
        Catch ex As Exception
            sLoiThongTin &= Environment.NewLine & "Chưa có địa chỉ khách hàng"
        End Try
        xlWorkSheet.Range("b13").Value = sthongtinkhachhang

        Try
            'tên tàu
            xlWorkSheet.Range("c18").Value = objHBL.tblFileMBL.tblMBL.tblVoyage.tblVessel.VesselName & " " & objHBL.tblFileMBL.tblMBL.tblVoyage.VoyageNo
        Catch ex As Exception
            sLoiThongTin &= Environment.NewLine & "Chưa có tên tàu"
        End Try

        Try
            'cảng nhận hàng
            xlWorkSheet.Range("c21").Value = objHBL.tblFileMBL.tblMBL.tblVoyage.tblPort1.Port
            xlWorkSheet.Range("b11").Value = objHBL.tblFileMBL.tblMBL.tblVoyage.tblPort1.Port
        Catch ex As Exception
            sLoiThongTin &= Environment.NewLine & "Chưa có cảng dỡ hàng"
        End Try

        Try
            'ngày nhận hàng
            xlWorkSheet.Range("c19").Value = objHBL.tblFileMBL.tblMBL.tblVoyage.ETD.Value.ToString("MMM dd,yyyy")
        Catch ex As Exception
            sLoiThongTin &= Environment.NewLine & "Chưa có ngày nhận hàng"
        End Try

        Try
            'B/L no
            xlWorkSheet.Range("c20").Value = objHBL.tblFileMBL.tblMBL.MBLNo
            If (objHBL.IsMaster = True) Then
                xlWorkSheet.Range("A26").Value = objHBL.tblFileMBL.tblMBL.MBLNo
            End If
        Catch ex As Exception
            sLoiThongTin &= Environment.NewLine & "Chưa có MBL No"
        End Try

        'thông tin container
        Dim rStart As Integer = 26
        If (objHBL.IsMaster = False) Then
            xlWorkSheet.Range("A26").Value = objHBL.HBLNo
        End If
        Dim ctrlCont As New CContainerController
        For Each cont As tblCont In ctrlCont.SelectContsByHBL(objHBL.ID_HBL)
            If (rStart > 30) Then
                'xlWorkSheet.Range((rStart - 2).ToString & ":" & (rStart - 1).ToString).Insert(Shift:=Excel.XlDirection.xlUp)
                'thêm 2 dòng mới vào
                xlWorkSheet.Rows(rStart - 1).Insert()
                xlWorkSheet.Rows(rStart - 1).Insert()
            End If
            xlWorkSheet.Range("B" & rStart.ToString).Value = cont.ContNo & "/" & cont.Seal
            'số kiện
            If (cont.Package.HasValue And cont.tblUnit IsNot Nothing) Then
                xlWorkSheet.Range("C" & rStart.ToString).Value = cont.Package.Value.ToString() & " " & cont.tblUnit.UnitName
            End If

            'mô tả hàng hóa
            If (cont.tblGood IsNot Nothing) Then
                xlWorkSheet.Range("D" & rStart.ToString).Value = cont.tblGood.GoodName
                xlWorkSheet.Range("D" & rStart.ToString, "E" & rStart.ToString).Merge()
            End If
            If (cont.GW.HasValue) Then
                xlWorkSheet.Range("F" & rStart.ToString).Value = cont.GW.Value.ToString("#,##0.##")
                xlWorkSheet.Range("G" & rStart.ToString).Value = "KGS"
            End If
            If (cont.CBM.HasValue) Then
                xlWorkSheet.Range("F" & (rStart + 1).ToString).Value = cont.CBM.Value.ToString("#,##0.##")
                xlWorkSheet.Range("G" & (rStart + 1).ToString).Value = "CBM"
            End If
            rStart = rStart + 2
        Next
        'format
        xlWorkSheet.Range("A26:" & "G" & (rStart - 1).ToString).Font.Bold = True
        xlWorkSheet.Range("A26:" & "G" & (rStart - 1).ToString).Font.Color = Color.Blue
        xlWorkSheet.Range("A26:" & "G" & (rStart - 1).ToString).Font.Size = 11
        With xlWorkSheet.Range("A26:" & "G" & (rStart - 1).ToString)
            .Font.Bold = True
            .Font.Color = Color.Blue
            .Font.Size = 11
            .VerticalAlignment = Excel.XlVAlign.xlVAlignTop
            .WrapText = True
        End With

        If (sLoiThongTin <> "Lỗi in Delivery Order:") Then
            MessageBox.Show(sLoiThongTin, "Thông báo")
        End If
    End Sub
    
#End Region

    Public Shared Function SplitString(ByVal sIn As String, ByVal iMaxLength As Integer) As ArrayList
        sIn.Trim()
        If sIn = "" Then
            Return Nothing
        End If
        Dim kq As New ArrayList
        While sIn.Length > iMaxLength
            Dim sKQ As String = sIn.Substring(0, iMaxLength)
            sKQ.Trim()
            sKQ = sKQ.Substring(0, sKQ.LastIndexOf(" "))
            kq.Add(sKQ)
            sIn = sIn.Substring(sKQ.Length + 1)
        End While
        kq.Add(sIn)
        Return kq
    End Function

    Public Shared Sub SplitString2(ByVal sIn As String, ByVal iMaxLength As Integer, ByRef kq As ArrayList)
        sIn.Trim()
        If sIn = "" Then
            Return
        End If
        While sIn.Length > iMaxLength
            Dim sKQ As String = sIn.Substring(0, iMaxLength)
            sKQ.Trim()
            sKQ = sKQ.Substring(0, sKQ.LastIndexOf(" "))
            kq.Add(sKQ)
            sIn = sIn.Substring(sKQ.Length + 1)
        End While
        kq.Add(sIn)
    End Sub
End Class
